﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class VehicleDao
    {
        public bool Delete(SqlConnection conn, int vehicleId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Vehicle_Usage] WHERE [VehicleId] = @VehicleId ");
            builder.Append("DELETE FROM [dbo].[Vehicle_Maintenance] WHERE [VehicleId] = @VehicleId ");
            builder.Append("DELETE FROM [dbo].[Vehicle] WHERE [VehicleId] = @VehicleId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@VehicleId", SqlDbType.Int) };
            commandParameters[0].Value = vehicleId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public string GetOperator(SqlConnection conn)
        {
            string commandText = "SELECT ISNULL(ParaValue,'') FROM Sys_Para WHERE ParaName='VEHICLE_OPERATOR'";
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText);
            if (obj2 != null)
            {
                return Convert.ToString(obj2);
            }
            return string.Empty;
        }

        public int Insert(SqlConnection conn, VehicleData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Vehicle] ");
            builder.Append("( ");
            builder.Append("\t[Number], ");
            builder.Append("\t[Model], ");
            builder.Append("\t[EngineNum], ");
            builder.Append("\t[Type], ");
            builder.Append("\t[Driver], ");
            builder.Append("\t[BuyDate], ");
            builder.Append("\t[Price], ");
            builder.Append("\t[Status], ");
            builder.Append("\t[Remark], ");
            builder.Append("\t[Photo] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@Number, ");
            builder.Append("\t@Model, ");
            builder.Append("\t@EngineNum, ");
            builder.Append("\t@Type, ");
            builder.Append("\t@Driver, ");
            builder.Append("\t@BuyDate, ");
            builder.Append("\t@Price, ");
            builder.Append("\t@Status, ");
            builder.Append("\t@Remark, ");
            builder.Append("\t@Photo ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[10];
            commandParameters[0] = new SqlParameter("@Number", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.Number;
            commandParameters[1] = new SqlParameter("@Model", SqlDbType.NVarChar, 20);
            if (data.ModelIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.Model;
            }
            commandParameters[2] = new SqlParameter("@EngineNum", SqlDbType.NVarChar, 50);
            if (data.EngineNumIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.EngineNum;
            }
            commandParameters[3] = new SqlParameter("@Type", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = data.Type;
            commandParameters[4] = new SqlParameter("@Driver", SqlDbType.NVarChar, 20);
            if (data.DriverIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Driver;
            }
            commandParameters[5] = new SqlParameter("@BuyDate", SqlDbType.DateTime);
            if (data.BuyDateIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.BuyDate;
            }
            commandParameters[6] = new SqlParameter("@Price", SqlDbType.NVarChar, 20);
            if (data.PriceIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Price;
            }
            commandParameters[7] = new SqlParameter("@Status", SqlDbType.NVarChar, 20);
            commandParameters[7].Value = data.Status;
            commandParameters[8] = new SqlParameter("@Remark", SqlDbType.NText);
            if (data.RemarkIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Remark;
            }
            commandParameters[9] = new SqlParameter("@Photo", SqlDbType.Image);
            if (data.PhotoIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.Photo;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[VehicleId], ");
            builder.Append("\t[Number], ");
            builder.Append("\t[Model], ");
            builder.Append("\t[EngineNum], ");
            builder.Append("\t[Type], ");
            builder.Append("\t[Driver], ");
            builder.Append("\t[BuyDate], ");
            builder.Append("\t[Price], ");
            builder.Append("\t[Status], ");
            builder.Append("\t[Remark], ");
            builder.Append("\t[Photo] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Vehicle] ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Vehicle";
            return set.Tables[0];
        }

        public DataTable SelectByVehicleId(SqlConnection conn, int vehicleId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("DECLARE @OrderNums\tint ");
            builder.Append("SELECT @OrderNums = COUNT(*) FROM [dbo].[Vehicle_Usage] WHERE [VehicleId] = @VehicleId ");
            builder.Append("SELECT ");
            builder.Append("\t[VehicleId], ");
            builder.Append("\t[Number], ");
            builder.Append("\t[Model], ");
            builder.Append("\t[EngineNum], ");
            builder.Append("\t[Type], ");
            builder.Append("\t[Driver], ");
            builder.Append("\t[BuyDate], ");
            builder.Append("\t[Price], ");
            builder.Append("\t[Status], ");
            builder.Append("\t[Remark], ");
            builder.Append("\tSUBSTRING([Photo],1,10) AS Photo, ");
            builder.Append("\t@OrderNums AS OrderNums ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Vehicle] ");
            builder.Append("WHERE ");
            builder.Append("\t[VehicleId] = @VehicleId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@VehicleId", SqlDbType.Int) };
            commandParameters[0].Value = vehicleId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Vehicle";
            return set.Tables[0];
        }

        public DataTable SelectOperator(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[Users].[UserId], ");
            builder.Append("\t[Users].[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Sys_Para] ");
            builder.Append("WHERE ");
            builder.Append("\tCHARINDEX(',' + [Users].[UserId] + ',',',' + CAST([Sys_Para].[ParaValue] AS varchar(2000)) + ',') >0 ");
            builder.Append("\tAND  [Sys_Para].[ParaName] = 'VEHICLE_OPERATOR' ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, VehicleData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Vehicle] ");
            builder.Append("SET ");
            builder.Append("\t[Number] = @Number, ");
            builder.Append("\t[Model] = @Model, ");
            builder.Append("\t[EngineNum] = @EngineNum, ");
            builder.Append("\t[Type] = @Type, ");
            builder.Append("\t[Driver] = @Driver, ");
            builder.Append("\t[BuyDate] = @BuyDate, ");
            builder.Append("\t[Price] = @Price, ");
            builder.Append("\t[Status] = @Status, ");
            builder.Append("\t[Remark] = @Remark\t ");
            builder.Append("WHERE ");
            builder.Append("\t[VehicleId] = @VehicleId ");
            builder.Append("IF @Photo IS NOT NULL ");
            builder.Append("\tUPDATE [dbo].[Vehicle] ");
            builder.Append("\tSET\t ");
            builder.Append("\t\t[Photo] = @Photo ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[VehicleId] = @VehicleId ");
            SqlParameter[] commandParameters = new SqlParameter[11];
            commandParameters[0] = new SqlParameter("@VehicleId", SqlDbType.Int);
            commandParameters[0].Value = data.VehicleId;
            commandParameters[1] = new SqlParameter("@Number", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.Number;
            commandParameters[2] = new SqlParameter("@Model", SqlDbType.NVarChar, 20);
            if (data.ModelIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.Model;
            }
            commandParameters[3] = new SqlParameter("@EngineNum", SqlDbType.NVarChar, 50);
            if (data.EngineNumIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.EngineNum;
            }
            commandParameters[4] = new SqlParameter("@Type", SqlDbType.NVarChar, 20);
            commandParameters[4].Value = data.Type;
            commandParameters[5] = new SqlParameter("@Driver", SqlDbType.NVarChar, 20);
            if (data.DriverIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.Driver;
            }
            commandParameters[6] = new SqlParameter("@BuyDate", SqlDbType.DateTime);
            if (data.BuyDateIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.BuyDate;
            }
            commandParameters[7] = new SqlParameter("@Price", SqlDbType.NVarChar, 20);
            if (data.PriceIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Price;
            }
            commandParameters[8] = new SqlParameter("@Status", SqlDbType.NVarChar, 20);
            commandParameters[8].Value = data.Status;
            commandParameters[9] = new SqlParameter("@Remark", SqlDbType.NText);
            if (data.RemarkIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.Remark;
            }
            commandParameters[10] = new SqlParameter("@Photo", SqlDbType.Image);
            if (data.PhotoIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.Photo;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public void UpdateOperator(SqlConnection conn, string strOperator)
        {
            string commandText = "UPDATE Sys_Para SET ParaValue = '" + strOperator + "'  WHERE ParaName='VEHICLE_OPERATOR'";
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText);
        }
    }
}

